package com.keli.dt.dao.impl;

import java.util.List;

import org.apache.log4j.Logger;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import com.keli.dt.dao.ILaneReportDao;
import com.keli.dt.model.LaneReport;
import com.keli.dt.model.RoadReport;
import com.keli.dt.model.bpss.LaneReportDeleteBPSS;
import com.keli.dt.model.bpss.LaneReportInsertBPSS;
import com.keli.dt.model.rowmapper.RoadReportRowMapper;

public class LaneReportDao extends JdbcDaoSupport implements ILaneReportDao {
	private Logger logger = Logger.getLogger(this.getClass());

	@Override
	public List<RoadReport> getRoadReportListByMaxReportTime() {
		String subsql = "(SELECT MAX(REPORT_TIME) FROM DT_LANE_REPORT_R)";
		return getRoadReportListByReportTime(subsql, null);
	}

	@Override
	public List<RoadReport> getRoadReportListByReportTime(String reportTime) {
		String subsql = "TO_DATE(?, 'YYYY/MM/DD HH24:MI:SS')";
		return getRoadReportListByReportTime(subsql, reportTime);
	}

	private String whereSubCondition(int index) {
		String format = " AND t%d.DETECT_CLASS = t.DETECT_CLASS AND t%d.ROAD_ID = t.ROAD_ID AND t%d.REPORT_TIME = t.REPORT_TIME";
		return String.format(format, index, index, index);
	}

	private List<RoadReport> getRoadReportListByReportTime(String subsql,
			String time) {
		StringBuffer sql = new StringBuffer();
		int index = 1;
		sql.append("SELECT DETECT_CLASS, ROAD_ID, TO_CHAR(REPORT_TIME, 'YYYY/MM/DD HH24:MI:SS') AS REPORT_TIME, ");
		sql.append("COUNT(DISTINCT LANE) AS TOTAL_LANE, ");
		sql.append("(SELECT SUM(VOLUME) FROM DT_LANE_REPORT_R t" + index); // t1
		sql.append(" WHERE VEHICLE_CLASS IN ('1', '2')");
		sql.append(whereSubCondition(index++));
		sql.append(") AS VOLUME_NONMOTOR, ");
		sql.append("(SELECT SUM(VOLUME) FROM DT_LANE_REPORT_R t" + index); // t2
		sql.append(" WHERE VEHICLE_CLASS IN ('3', '4') AND t2.DETECT_CLASS = t.DETECT_CLASS ");
		sql.append(whereSubCondition(index++));
		sql.append(" AND t2.ROAD_ID = t.ROAD_ID AND t2.REPORT_TIME = t.REPORT_TIME) AS VOLUME_MOTOR, ");
		sql.append("(SELECT AVG(SPEED) FROM DT_LANE_REPORT_R t" + index); // t3
		sql.append(" WHERE VEHICLE_CLASS IN ('3', '4')");
		sql.append(whereSubCondition(index++));
		sql.append(") AS SPEED, ");
		sql.append("(SELECT COUNT(DISTINCT LANE) FROM DT_LANE_REPORT_R t"
				+ index); // t4
		sql.append(" WHERE VEHICLE_CLASS IN ('1', '2')");
		sql.append(whereSubCondition(index++));
		sql.append(") AS NONMOTOR_LANE, ");
		sql.append("(SELECT COUNT(DISTINCT LANE) FROM DT_LANE_REPORT_R t"
				+ index); // t5
		sql.append(" WHERE VEHICLE_CLASS IN ('3', '4')");
		sql.append(whereSubCondition(index++));
		sql.append(") AS MOTOR_LANE, ");
		sql.append("(SELECT AVG(TO_NUMBER(DENSITY, '999.999')) FROM DT_LANE_REPORT_R t"
				+ index); // t6
		sql.append(" WHERE VEHICLE_CLASS IN ('3', '4') ");
		sql.append(whereSubCondition(index++));
		sql.append(") AS DENSITY, TO_CHAR(MAX(COLLEC_DATETIME), 'YYYY/MM/DD HH24:MI:SS') AS COLLEC_DATETIME");
		sql.append(" FROM DT_LANE_REPORT_R t WHERE REPORT_TIME = ");
		sql.append(subsql);
		sql.append(" GROUP BY DETECT_CLASS, ROAD_ID, REPORT_TIME");

		logger.debug(sql.toString());

		if (time == null) {
			return this.getJdbcTemplate().query(sql.toString(),
					new RoadReportRowMapper());
		} else {
			return this.getJdbcTemplate().query(sql.toString(),
					new RoadReportRowMapper(), time);
		}
	}

	@Override
	public int delete(String detectClass, String detectId, String roadId,
			String lane) {
		String sql = LaneReportDeleteBPSS.deleteSql();

		logger.debug(sql.toString());
		System.out.println(String.format("%s, %s, %s, %s\n", detectClass,
				detectId, roadId, lane));

		return this.getJdbcTemplate().update(sql, detectClass,
				detectId, roadId, lane);
	}

	@Override
	public int insert(LaneReport laneReport, String reportTime) {
		// 删除实时表
		delete(laneReport.getDETECT_CLASS(), laneReport.getDETECT_ID(),
				laneReport.getROAD_ID().toString(), laneReport.getLANE());

		// 插入实时表
		String realTimeSQL =  LaneReportInsertBPSS.insertRealTimeSql();
		insert(realTimeSQL, laneReport, reportTime);
		// 插入历史表
		String historySQL = LaneReportInsertBPSS.insertHistorySql();
		insert(historySQL, laneReport, reportTime);

		return 1;
	}

	/**
	 * 插入车道新信息
	 * @param sql 插入语句
	 * @param lr 车道信息对象
	 * @param reportTime 上报时间
	 * @return
	 */
	private int insert(String sql, LaneReport lr, String reportTime) {
		logger.debug(sql);

		return this.getJdbcTemplate().update(sql, lr.getDETECT_CLASS(),
				lr.getDETECT_ID(), lr.getROAD_ID(), lr.getLANE(),
				lr.getVEHICLE_CLASS(), lr.getSPEED(), lr.getDENSITY(),
				lr.getVOLUME(), lr.getCYCLE(), lr.getCOLLEC_TIME(),
				lr.getCOLLEC_DATETIME(), reportTime);
	}

	@Override
	public int insert(List<LaneReport> laneReports, String reportTime) {
		int i = 0;
		logger.debug("insert DT_LANE_REPORT_H&R start\n");
		deleteWithBatchUpdate(laneReports);
		// 插入实时表
		String realTimeSQL =  LaneReportInsertBPSS.insertRealTimeSql();
		insertWithBatchUpdate(realTimeSQL, laneReports, reportTime);
		// 插入历史表
		String historySQL = LaneReportInsertBPSS.insertHistorySql();
		insertWithBatchUpdate(historySQL, laneReports, reportTime);
		logger.debug("insert DT_LANE_REPORT_H&R done\n");

		return laneReports.size();
	}

	private int insertWithBatchUpdate(String sql,
			List<LaneReport> laneReports, String reportTime) {
		logger.debug(sql);
		// 执行批量sql 处理多次插入操作
		int[] count = this.getJdbcTemplate().batchUpdate(sql,
				new LaneReportInsertBPSS(laneReports, reportTime));

		return 1;
	}
	
	private int deleteWithBatchUpdate(List<LaneReport> laneReports) {
		String sql = LaneReportDeleteBPSS.deleteSql();		
		logger.debug(sql);		
		int[] count = this.getJdbcTemplate().batchUpdate(sql, new LaneReportDeleteBPSS(laneReports));
		
		return 1;
	}
}
